--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_SCALE_LOAF.HQL
--    Functions : 表6：贷款发生额企业规模期限结构表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_SCALE_LOAF PARTITION (DATA_DATE = '20180331')
    SELECT  XY.FIN_ORG_DIST
           ,XY.CCY
           ,XY.ENTERPRISE_SCALE
           ,XY.SCALE_DESC
           ,SUM(XY.AMOUNT_0_6M    )/100000000
           ,SUM(XY.WSUM_0_6M      )/100000000
           ,SUM(XY.AMOUNT_6_12M   )/100000000
           ,SUM(XY.WSUM_6_12M     )/100000000
           ,SUM(XY.AMOUNT_1_3Y    )/100000000
           ,SUM(XY.WSUM_1_3Y      )/100000000
           ,SUM(XY.AMOUNT_3_5Y    )/100000000
           ,SUM(XY.WSUM_3_5Y      )/100000000
           ,SUM(XY.AMOUNT_5_10Y   )/100000000
           ,SUM(XY.WSUM_5_10Y     )/100000000
           ,SUM(XY.AMOUNT_10_999Y )/100000000
           ,SUM(XY.WSUM_10_999Y   )/100000000
           ,SUM(XY.AMOUNT_ALL     )/100000000
           ,SUM(XY.WSUM_ALL       )/100000000
    FROM (
        -- 大型企业 中型企业 小型企业 微型企业
        SELECT
         AREA.AREA_CODE_4                                                             AS FIN_ORG_DIST
        ,FSE.CCY                                                                      AS CCY
        ,FSE.ENTERPRISE_SCALE                                                         AS ENTERPRISE_SCALE    -- 企业规模
        ,CASE WHEN FSE.ENTERPRISE_SCALE='CS01' THEN '大型企业'
              WHEN FSE.ENTERPRISE_SCALE='CS02' THEN '中型企业'
              WHEN FSE.ENTERPRISE_SCALE='CS03' THEN '小型企业'
              WHEN FSE.ENTERPRISE_SCALE='CS04' THEN '微型企业' 
         END                                                                          AS SCALE_DESC
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_0_6M            --6个月（含）以内-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_6_12M           --6个月至1年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_1_3Y            --1至3年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_3_5Y            --3至5年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_5_10Y           --5至10年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_10_999Y         --10年以上-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                 AS AMOUNT_ALL             --所有期限-贷款发生额
        ,COALESCE(FSE.WSUM_AMOUNT,0)                                                  AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DRAWDOWN_OR_REPAY='1' AND ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04') AND DATA_DATE='20180331')FSE
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

        UNION ALL

        -- 合计
        SELECT
         AREA.AREA_CODE_4                                                               AS FIN_ORG_DIST
        ,LOAF.CCY                                                                       AS CCY
        ,'Z'                                                                            AS ENTERPRISE_SCALE    -- 企业规模
        ,'合计'                                                                         AS SCALE_DESC
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='1' THEN LOAF.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_0_6M            --6个月（含）以内-贷款发生额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='1' THEN LOAF.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='2' THEN LOAF.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_6_12M           --6个月至1年（含）-贷款发生额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='2' THEN LOAF.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='3' THEN LOAF.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_1_3Y            --1至3年（含）-贷款发生额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='3' THEN LOAF.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='4' THEN LOAF.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_3_5Y            --3至5年（含）-贷款发生额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='4' THEN LOAF.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='5' THEN LOAF.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_5_10Y           --5至10年（含）-贷款发生额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='5' THEN LOAF.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='6' THEN LOAF.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_10_999Y         --10年以上-贷款发生额
        ,COALESCE(CASE WHEN LOAF.TERM_CODE ='6' THEN LOAF.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(LOAF.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL             --所有期限-贷款发生额
        ,COALESCE(LOAF.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DRAWDOWN_OR_REPAY='1' AND ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04') AND DATA_DATE='20180331') LOAF
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON LOAF.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
        WHERE LOAF.DATA_DATE='20180331'

        UNION ALL

        -- 中小企业
        SELECT
         AREA.AREA_CODE_4                                                             AS FIN_ORG_DIST
        ,FSE.CCY                                                                      AS CCY
        ,'CS234'                                                                       AS ENTERPRISE_SCALE    -- 企业规模
        ,'中小企业'                                                                   AS SCALE_DESC
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_0_6M            --6个月（含）以内-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_6_12M           --6个月至1年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_1_3Y            --1至3年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_3_5Y            --3至5年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_5_10Y           --5至10年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_10_999Y         --10年以上-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                 AS AMOUNT_ALL             --所有期限-贷款发生额
        ,COALESCE(FSE.WSUM_AMOUNT,0)                                                  AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DRAWDOWN_OR_REPAY='1' AND ENTERPRISE_SCALE IN('CS02','CS03','CS04') AND DATA_DATE='20180331')FSE
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

        UNION ALL

        -- 小微企业
        SELECT
         AREA.AREA_CODE_4                                                             AS FIN_ORG_DIST
        ,FSE.CCY                                                                      AS CCY
        ,'CS34'                                                                       AS ENTERPRISE_SCALE    -- 企业规模
        ,'小微企业'                                                                   AS SCALE_DESC
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_0_6M            --6个月（含）以内-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_6_12M           --6个月至1年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_1_3Y            --1至3年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_3_5Y            --3至5年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_5_10Y           --5至10年（含）-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)    AS AMOUNT_10_999Y         --10年以上-贷款发生额
        ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT  ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
        ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                 AS AMOUNT_ALL             --所有期限-贷款发生额
        ,COALESCE(FSE.WSUM_AMOUNT,0)                                                  AS WSUM_ALL            --所有期限-加权金额
        FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DRAWDOWN_OR_REPAY='1' AND ENTERPRISE_SCALE IN('CS03','CS04') AND DATA_DATE='20180331')FSE
        -- 与地区表最细级关联
        LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
        LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    ) XY
    GROUP BY XY.FIN_ORG_DIST
            ,XY.CCY
            ,XY.ENTERPRISE_SCALE
            ,XY.SCALE_DESC;